﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.ApplicationBlocks.Data;
using System.Configuration;

namespace ModuleDuoc
{
    public partial class frmMaVachKhac : Form
    {
        static string _ten;
        public frmMaVachKhac()
        {
            InitializeComponent();
            dgvData.AutoGenerateColumns = false;
        }
        public frmMaVachKhac(string ten)
        {
            InitializeComponent();
            _ten = ten;
        }

        private void frmMaVachKhac_Load(object sender, EventArgs e)
        {
            getByNameSearch();
            getSLTonByTenVT();
        }
        private void getByNameSearch()
        {
            string sql = "SELECT zdp.mavach, zdp.hansd FROM zD_PHIEUNHAPKHOCT zdp " +
                            "INNER JOIN zD_PHIEUNHAPKHO zdp2 ON zdp2.sophieu = zdp.sophieu " +
                            "INNER JOIN zD_DM_THUOC zddt ON zddt.mavt = zdp.mavt " +
                            "WHERE zddt.NameSearch LIKE '%" + _ten + "%' " +
                            "AND CONVERT(DATETIME, zdp2.ngaylap, 103) > CONVERT(DATETIME, '01/" + DateTime.Now.Month + "/2013 00:00:00', 103) ";
                            //"ORDER BY zdp.hansd";
            DataTable dt = new DataTable();
            dt = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["Main.ConnectionString"], CommandType.Text, sql).Tables[0];
            //this.dgvData.DataSource = dt;
            for(int i =0; i< dt.Rows.Count; i++)
            {
                dgvData.Rows.Add(dt.Rows[i]["MaVach"], "", dt.Rows[i]["HanSD"]);
            }
        }

        private void getSLTonByTenVT()
        {
            for (int i = 0; i < dgvData.Rows.Count; i++)
            {
                string sql = "SELECT(( SELECT ISNULL(SUM(zdp.soluong), 0) AS SLNhap " +
                            "FROM zD_PHIEUNHAPKHOCT zdp INNER JOIN zD_PHIEUNHAPKHO zdp2 " +
                            "ON zdp2.sophieu = zdp.sophieu " +
                            "WHERE zdp2.huy = 0 AND  zdp.mavach = " + dgvData.Rows[i].Cells["MaVach"].Value.ToString() + " AND CONVERT(DATETIME, zdp2.ngaylap, 103) > CONVERT(DATETIME, '01/" + DateTime.Now.Month + "/2013 00:00:00', 103)) + " +

                            "(SELECT ISNULL(sum(zdk.soluong), 0) FROM zD_KIEMKE2012 zdk " +
                            "WHERE zdk.mavach = " + dgvData.Rows[i].Cells["MaVach"].Value.ToString() + " AND zdk.thang = " + DateTime.Now.Month + " AND zdk.nam = 2013) - " +

                            "(SELECT ISNULL(sum(zdp.soluong), 0) AS SLXuat " +
                            "FROM zD_PHIEUXUATKHOCT zdp INNER JOIN zD_PHIEUXUATKHO zdp2 " +
                            "ON zdp2.sophieu = zdp.sophieu " +
                            "WHERE zdp2.huy = 0 AND  zdp.mavach = " + dgvData.Rows[i].Cells["MaVach"].Value.ToString() + "  and CONVERT(DATETIME, zdp2.ngaylap, 103) > CONVERT(DATETIME, '01/" + DateTime.Now.Month + "/2013 00:00:00', 103)) +" +

                            "(SELECT ISNULL(SUM(zdptlc.SoLuongTraLai), 0) AS SLTra FROM zD_PhieuTraLaiCT zdptlc " +
                            "INNER JOIN zD_PhieuTraLai zdptl ON zdptl.SoHoaDonBanLe = zdptlc.SoHoaDonBanLe " +
                            "WHERE zdptlc.MaVach = " + dgvData.Rows[i].Cells["MaVach"].Value.ToString() + " and CONVERT(DATETIME, zdptl.ngaylap, 103) > " +
                            "CONVERT(DATETIME, '01/" + DateTime.Now.Month + "/2013 00:00:00', 103)) " +
                            ") AS SL";
                DataTable dt = new DataTable();
                dt = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["Main.ConnectionString"], CommandType.Text, sql).Tables[0];
                this.dgvData.Rows[i].Cells["SoLuong"].Value = dt.Rows[0][0].ToString();
            }
            for (int i = 0; i < this.dgvData.Rows.Count; i++)
            {
                if (Int32.Parse(dgvData.Rows[i].Cells["SoLuong"].Value.ToString()) < 0)
                {
                    this.dgvData.Rows.RemoveAt(this.dgvData.Rows[i].Index);
                }
            }
        }
    }
}
